// 仓库分区的数据模型
const util = require("../../common/util");
// 创建 MySQL 连接池
const pool = require("../../common/util");

class WarehouseZoneDB {
    constructor() {
        this.instance = null;
        this.pool = pool;
    }
    static getInstance() {
        if (!this.instance) {
            this.instance = new WarehouseZoneDB();
        }
        return this.instance;
    }

    // 添加仓库区域
    async add(warehouseZone) {
        try {
            const sql = "INSERT INTO warehousezone (warehouse_id, name,capacity, occupied_space,zone_type,zone_manager_id, location_guide, safety_stock_level, last_audit_date, comments) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,?)";
            const params = [
                warehouseZone.warehouseId,
                warehouseZone.name,
                warehouseZone.capacity,
                warehouseZone.occupiedSpace,
                warehouseZone.zoneType,
                warehouseZone.zoneManagerId,
                warehouseZone.locationGuide,
                warehouseZone.safetyStockLevel,
                warehouseZone.lastAuditDate,
                warehouseZone.comments
            ];
            const result = await this.pool.query(sql, params);
            return result.insertId;
        } catch (err) {
            throw new Error(`Error adding warehouse zone: ${err.message}`);
        }
    }

    // 修改仓库区域  
    async update(warehouseZone) {
        try {
            // 假设 warehouseZone 包含一个 id 属性，用于标识要更新的记录  
            const sql = "UPDATE warehousezone SET warehouse_id = ?, name = ?, capacity = ?, occupied_space = ?, zone_type = ?, zone_manager_id = ?, location_guide = ?, safety_stock_level = ?, last_audit_date = ?, comments = ? WHERE id = ?";
            const params = [
                warehouseZone.warehouseId, // 注意：这里假设 warehouse_id 是仓库区域的属性之一，但通常更新时不会修改它  
                warehouseZone.name,
                warehouseZone.capacity,
                warehouseZone.occupiedSpace,
                warehouseZone.zoneType,
                warehouseZone.zoneManagerId,
                warehouseZone.locationGuide,
                warehouseZone.safetyStockLevel,
                warehouseZone.lastAuditDate,
                warehouseZone.comments,
                warehouseZone.id // 这里应该是用于标识要更新的仓库区域的唯一ID  
            ];

            // 注意：这里没有使用 result.insertId，因为 UPDATE 操作不返回插入的ID  
            const result = await this.pool.query(sql, params);

            // 通常 UPDATE 操作返回受影响的行数，可以根据需要返回这个结果或其他的确认信息  
            // 例如，如果受影响的行数为0，可能表示没有找到要更新的记录  
            if (result.affectedRows === 0) {
                throw new Error('No warehouse zone was updated.');
            }

            // 返回确认信息或受影响的行数  
            return { affectedRows: result.affectedRows };
        } catch (err) {
            throw new Error(`Error updating warehouse zone: ${err.message}`);
        }
    }
    // 根据ID查询仓库区域
    async find(id) {
        try {
            const sql = "SELECT w.*, u.truename FROM warehousezone w LEFT JOIN users u ON w.zone_manager_id = u.id WHERE w.id = ?;";
            const params = [id];
            const result = await this.pool.query(sql, params);
            return result[0];
        } catch (err) {
            throw new Error(`Error finding warehouse zone by ID: ${err.message}`);
        }
    }

    // 获取仓库区域总数
    async getCount() {
        try {
            const sql = "SELECT COUNT(*) as total FROM warehousezone";
            const result = await this.pool.query(sql);
            return result[0].total;
        } catch (err) {
            throw new Error(`Error getting warehouse zone count: ${err.message}`);
        }
    }

    // 根据仓库类型模糊查询仓库区域    
    // zoneType: 仓库类型的一部分，用于模糊查询条件    
    async findByZoneTypeLike(zoneType) {
        try {
            // 使用参数化查询来避免 SQL 注入    
            // 注意：这里我们直接在 zoneType 前后添加 % 来实现模糊匹配  
            const sql = "SELECT w.*, u.truename FROM warehousezone w LEFT JOIN users u ON w.zone_manager_id = u.id WHERE w.zone_type LIKE ?;";
            // 使用 CONCAT 函数（取决于数据库是否支持）或者直接在字符串中拼接 '%'  
            // 例如，如果数据库支持 CONCAT，可以这样写：CONCAT('%', ?, '%')  
            // 但为了简洁和兼容性，这里直接在参数中拼接  
            const params = [`%${zoneType}%`];

            // 执行查询并获取结果    
            const results = await this.pool.query(sql, params);

            // 返回所有匹配的仓库区域    
            return results;
        } catch (err) {
            // 记录错误到日志系统（这里只是简单地打印到控制台）    
            console.error(`Error finding warehouse zones by zone type: ${err.message}`);

            // 抛出错误以便调用者可以捕获和处理    
            throw new Error(`Error finding warehouse zones by zone type: ${err.message}`);
        }
    }
    // 删除仓库区域
    async remove(id) {
        try {
            const sql = "DELETE FROM warehousezone WHERE id = ?";
            const params = [id];
            const result = await this.pool.query(sql, params);
            return result.affectedRows > 0;
        } catch (err) {
            throw new Error(`Error deleting warehouse zone: ${err.message}`);
        }
    }

    /**
     * 
     * @param {*} limit 
     * @param {*} offset 
     * @returns 
     */
    async findAll(limit = -1, offset = -1) {
        try {
            let sql = "SELECT * FROM warehousezone";
            const params = [];

            if (limit !== -1) {
                sql += " LIMIT ?";
                params.push(limit);

                if (offset !== -1) {
                    sql += " OFFSET ?";
                    params.push(offset);
                }
            }

            const result = await this.pool.query(sql, params);
            return result;
        } catch (err) {
            throw new Error(`Error getting warehouse zone list: ${err.message}`);
        }
    }

/**  
 *   
 * @param {number} limit - 要获取的仓库区域数量，默认为-1表示获取所有  
 * @param {number} offset - 跳过的仓库区域数量，默认为-1表示不跳过  
 * @returns {Promise<Array>} - 仓库区域信息的数组  
 */  
async findMinShelves(limit = -1, offset = -1) {  
    try {  
        // 构建子查询以获取每个仓库区域的货架数量  
        let subquery = "SELECT warehouseZone_id, COUNT(s.id) AS shelf_count " +  
            "FROM shelves s " +  
            "JOIN WarehouseZone w ON s.warehouseZone_id = w.id " +  
            "GROUP BY s.warehouseZone_id";  
  
        // 构建主查询以按货架数量升序排序并选择指定数量的仓库区域  
        let sql = "SELECT w.* " +  
            "FROM WarehouseZone w " +  
            "JOIN (" + subquery + ") AS sub ON w.id = sub.warehouseZone_id " +  
            "ORDER BY sub.shelf_count ASC";  
  
            const params = [];
        // 添加LIMIT和OFFSET（如果提供了）  
        if (limit !== -1) {
            sql += " LIMIT ?";
            params.push(limit);

            if (offset !== -1) {
                sql += " OFFSET ?";
                params.push(offset);
            }
        }
  
        // 执行查询  
        const result = await this.pool.query(sql, params); 
        console.log(result);
        return result;  
    } catch (err) {  
        throw new Error(`Error getting warehouse zone list with least shelves: ${err.message}`);  
    }  
}

    //获取指定仓库区域的的货架列表
    async findShelvesByZoneName(name, limit = -1, offset = -1) {
        try {
            let sql = "SELECT s.*, w.name as warehouseZone_name FROM shelves s LEFT JOIN WarehouseZone w ON s.warehouseZone_id = w.id WHERE w.name LIKE ?";
            const params = [`%${name}%`];
            if (limit !== -1) {
                sql += " LIMIT ?";
                params.push(limit);

                if (offset !== -1) {
                    sql += " OFFSET ?";
                    params.push(offset);
                }
            }
            const result = await this.pool.query(sql, params);
            return result;
        } catch (err) {
            throw new Error(`Error getting shelves by warehouse zone: ${err.message}`);
        }
    }

    // 根据仓库区域的名字限制和偏移查询仓库区域
    async findByKeyword(name, limit = -1, offset = -1) {
        try {
            // 构造查询仓库区域的 SQL 语句
            let sql = "SELECT w.*, u.truename FROM warehousezone w LEFT JOIN users u ON w.zone_manager_id = u.id WHERE w.name LIKE ?;";
            const params = ['%' + name + '%'];

            // 如果需要，添加限制和偏移
            if (limit !== -1) {
                sql += " LIMIT ?";
                params.push(limit);

                if (offset !== -1) {
                    sql += " OFFSET ?";
                    params.push(offset);
                }
            }

            // 使用 query 函数执行 SQL 查询操作
            const result = await this.pool.query(sql, params);
            return result;
        } catch (err) {
            // 在出现错误时抛出异常
            throw new Error(`Error finding warehouse zone by keyword: ${err.message}`);
        }
    }
}

module.exports = WarehouseZoneDB;
